package com.wjw.utils.database.sqlite;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @desc SQLite数据库操作类
 * @author wjw
 * @date 2016年11月18日下午4:32:41
 */
public final class SQLiteDB{
	
    /**
     * 日志对象
     */
    protected static Logger log = LoggerFactory.getLogger(SQLiteDB.class);
    
	private static Connection conn=null;//db链接对象
	
	private SQLiteDB(){}
	
	//静态块初始化时加载驱动
	static{
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取数据库连接
	 * @return
	 */
	public static Connection getConn(String path){
		if (null == conn) {
			try {
				conn = DriverManager.getConnection("jdbc:sqlite:" + path,"username","password");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return conn;
	}

	/**
	 * 判断表或视图是否存在。注意，返回结果后连接Connection并没有关闭，需手动关闭
	 * @param conn 连接
	 * @param name 表或视图名
	 * @return
	 */
	public static boolean isExists(Connection conn, String name) {
		boolean flg = false;
		DatabaseMetaData meta;
		try {
			meta = conn.getMetaData();
			ResultSet rsTables = meta.getTables(null, null, name, null);
			if (rsTables.next()) {
				flg = true;
			}
		} catch (SQLException e) {
			System.out.println("查询错误:查询异常");
			e.printStackTrace();
		}
		return flg;
	}
	
    /**
     * 创建表 ，链接未关闭
     * @param tableName
     * @param createTableSql
     */
	public static void createTable(Connection conn,String tableName, String createTableSql) {
        if (!isExists(conn, tableName)) {
            //执行SQL语句
        	executeSQL(conn,createTableSql);
        }else{
        	throw new RuntimeException("无法创建,表已经存在!");
        }
    }
    
	/**
	 * 执行sql,返回boolean类型。注意:数据库连接未关闭，需要手动关闭
	 * @param conn  数据库连接
	 * @param sql  要执行的sql语句
	 * @return
	 */
	public static boolean executeSQL(Connection conn, String sql) {
		log.info("要执行的sql:"+sql);
		PreparedStatement pst = null;
		boolean flg=false;
		try {
			if (conn == null || conn.isClosed() || sql == null
					|| "".equals(sql.trim())) {
				return flg;
			}
			pst = conn.prepareStatement(sql);
			pst.executeUpdate();
			conn.commit();
			flg=true;
		} catch (SQLException e) {
			e.printStackTrace();
			log.info("执行sql出错:"+sql);
			log.info(e.getMessage());
			flg=false;
		} finally {
			close(null,null,pst);
		}
		return flg;
	}
	
	/**
	 * 关闭数据库连接
	 * @param conn
	 * @param rs
	 * @param st
	 */
	public static void close(Connection conn,ResultSet rs, Statement st) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
